package com.ruoyi.kycinfo.service.impl;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;

import com.ruoyi.common.utils.DateUtils;
import com.ruoyi.kycinfo.domain.*;
import com.ruoyi.kycinfo.service.IKycUserInfoService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.ruoyi.kycinfo.mapper.KycFinanceMapper;
import com.ruoyi.kycinfo.service.IKycFinanceService;
import org.springframework.transaction.annotation.Transactional;

/**
 * kyc金融信息Service业务层处理
 *
 * @author kriby
 */
@Service
public class KycFinanceServiceImpl implements IKycFinanceService {
    @Autowired
    private IKycUserInfoService kycUserInfoService;

    @Autowired
    private KycFinanceMapper kycFinanceMapper;

    /**
     * 查询kyc金融信息
     *
     * @param financeId kyc金融信息主键
     * @return kyc金融信息
     */
    @Override
    public KycFinance selectKycFinanceByFinanceId(Long financeId) {
        return kycFinanceMapper.selectKycFinanceByFinanceId(financeId);
    }

    @Override
    public KycFinance selectKycFinanceByUserInfoId(Long userInfoId) {
        return kycFinanceMapper.selectKycFinanceByUserInfoId(userInfoId);
    }

    /**
     * 查询kyc金融信息列表
     *
     * @param kycFinance kyc金融信息
     * @return kyc金融信息
     */
    @Override
    public List<KycFinance> selectKycFinanceList(KycFinance kycFinance) {
        return kycFinanceMapper.selectKycFinanceList(kycFinance);
    }

    /**
     * 新增kyc金融信息
     *
     * @param kycFinance kyc金融信息
     * @return 结果
     */
    @Override
    public int insertKycFinance(KycFinance kycFinance) {
        kycFinance.setCreateTime(DateUtils.getNowDate());
        return kycFinanceMapper.insertKycFinance(kycFinance);
    }

    /**
     * 修改kyc金融信息
     *
     * @param kycFinance kyc金融信息
     * @return 结果
     */
    @Override
    public int updateKycFinance(KycFinance kycFinance) {
        kycFinance.setUpdateTime(DateUtils.getNowDate());
        return kycFinanceMapper.updateKycFinance(kycFinance);
    }

    /**
     * 批量删除kyc金融信息
     *
     * @param financeIds 需要删除的kyc金融信息主键
     * @return 结果
     */
    @Override
    public int deleteKycFinanceByFinanceIds(Long[] financeIds) {
        return kycFinanceMapper.deleteKycFinanceByFinanceIds(financeIds);
    }

    /**
     * 删除kyc金融信息信息
     *
     * @param financeId kyc金融信息主键
     * @return 结果
     */
    @Override
    public int deleteKycFinanceByFinanceId(Long financeId) {
        return kycFinanceMapper.deleteKycFinanceByFinanceId(financeId);
    }

    /*
     * 读取excel文件，导入数据到数据库
     * */
    @Transactional
    @Override
    public String importKycFinance(boolean updateSupport, InputStream inputStream) throws IOException {
        Workbook workbook = new XSSFWorkbook(inputStream);
        Sheet sheet = workbook.getSheetAt(0); // 获取第一个Sheet页
        int rows = sheet.getLastRowNum();

        KycFinance kycFinance = new KycFinance();
        List<Long> existUserInfoIdList = new ArrayList<>();

        // 这里的 rows 是从0开始的，所以要取等于号
        for (int i = 1; i <= rows; i++) {
            Row row = sheet.getRow(i);
            if (row != null && !isEmptyRow(row)) {//判断每一行是否是空行
                kycFinance.setUserInfoId(getLongValue(row.getCell(0)));
                kycFinance.setMonAssetAver((getValue(row.getCell(1))));
                kycFinance.setMonDepositAver((getValue(row.getCell(2))));
                kycFinance.setMonTrustAver((getValue(row.getCell(3))));
                kycFinance.setMonInsuranceAver((getValue(row.getCell(4))));
                kycFinance.setMonFundAver((getValue(row.getCell(5))));
                kycFinance.setAnnAssetAver((getValue(row.getCell(6))));
                kycFinance.setAnnDepositAver((getValue(row.getCell(7))));
                kycFinance.setAnnTrustAver((getValue(row.getCell(8))));
                kycFinance.setAnnInsuranceAver((getValue(row.getCell(9))));
                kycFinance.setAnnFundAver((getValue(row.getCell(10))));
                kycFinance.setLoanBalance((getValue(row.getCell(11))));
                kycFinance.setAssetBalance((getValue(row.getCell(12))));
                kycFinance.setDepositBalance((getValue(row.getCell(13))));
                kycFinance.setTrustBalance((getValue(row.getCell(14))));
                kycFinance.setFundBalance((getValue(row.getCell(15))));
                kycFinance.setIsValid(getStringValue(row.getCell(16)));
                kycFinance.setCurrentRate(getStringValue(row.getCell(17)));
                kycFinance.setOverdraftBalance((getValue(row.getCell(18))));
                kycFinance.setMovementsCount(getLongValue(row.getCell(19)));
//                kycFinance.setRemark(getStringValue(row.getCell(20)));
//                System.out.println(kycFinance);

                // 查询该客户号客户的金融信息是否已经存在
                Long userInfoId = kycFinance.getUserInfoId();
                KycFinance finance = this.selectKycFinanceByUserInfoId(userInfoId);
                if (finance == null) {
                    this.insertKycFinance(kycFinance);
//                    System.out.println("新增客户号'" + userInfoId + "'金融信息");
                } else {
                    // 更新客户金融信息
                    if (updateSupport) {
                        // 设置finance主键id，根据主键id进行update操作
                        kycFinance.setFinanceId(finance.getFinanceId());
                        this.updateKycFinance(kycFinance);
//                        System.out.println("更新客户号'" + userInfoId + "'金融信息");
                    } else {
                        // 用户输入的客户号用户信息已经存在，返回错误信息
                        existUserInfoIdList.add(userInfoId);
//                        System.out.println("新增客户号'" + userInfoId + "'金融信息失败，该客户金融信息已存在");
                    }
                }
//                System.out.println("读取第" + i + "行数据成功！");
            }

        }
        // 关闭输入流
        inputStream.close();

        if (existUserInfoIdList.size() == 0) {
            return "文件导入成功";
        } else {
            return "文件导入成功！部分客户号金融数据已存在，已自动忽略该客户金融信息的导入，请您检查客户号:" + existUserInfoIdList+"<br>如需更新客户金融信息，请选择更新已经存在的用户数据";
        }
    }

    private boolean isEmptyRow(Row row) {
        for (Cell cell : row) {
            if (cell.getCellType() != CellType.BLANK) {
                return false; // 存在非空单元格，不是空行
            }
        }
        return true; // 所有单元格都是空单元格，是空行
    }

    public static BigDecimal getValue(Cell cell) {
        if (cell == null || cell.getCellType() == CellType.BLANK) {
            return new BigDecimal(0);
        }
        CellType cellType = cell.getCellType();
        switch (cellType) {

            case NUMERIC:
                return BigDecimal.valueOf(cell.getNumericCellValue());
            case BOOLEAN:
            case FORMULA:
            case ERROR:
            case STRING:
            default:
                return new BigDecimal(0);
        }
    }

    public static String getStringValue(Cell cell) {
        if (cell == null || cell.getCellType() == CellType.BLANK) {
            return null;
        }
        CellType cellType = cell.getCellType();
        if (cellType == CellType.STRING) {
            return cell.getStringCellValue();
        } else {
            return null;
        }
    }

    public static Long getLongValue(Cell cell) {
        if (cell == null || cell.getCellType() == CellType.BLANK) {
            return 0L;
        }
        CellType cellType = cell.getCellType();
        if (cellType == CellType.NUMERIC)
            return (long) cell.getNumericCellValue();
        else if (cellType == CellType.STRING) {
            try {
                return Long.parseLong(cell.getStringCellValue());
            } catch (NumberFormatException e) {
                return -1L;
            }
        }
        return -1L;
    }
}
